package com.demo.util;

import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLType;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import java.sql.PreparedStatement;

public class JDBCUtils {

	static Connection conn;
	static Statement stmt;//代表一条SQL
	static PreparedStatement ps;//代表一条预编译的SQL，传参方便，性能好点，防止SQL注入的问题
	static ResultSet rs;//代表结果集
	static Properties properties;
	
	static {
		try {
			properties = new Properties();
			InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");//使用类加载器加载属性文件
			properties.load(is);//读取属性文件的数据存入到Properties
			Class.forName(properties.getProperty("jdbc.driverClassName"));//加载mysql驱动包
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConnection() {	
		try {
			conn = DriverManager.getConnection(properties.getProperty("jdbc.url"), 
					properties.getProperty("jdbc.username"), properties.getProperty("jdbc.password"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 执行增删改
	 * @param args
	 */
	public static int executeUpdate(String sql, Object... params) {
		int nums = 0;
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			int i=0;
			for(Object p : params) {
				ps.setObject(i+1, p);//给?占位符绑定参数值,从1开始
				i++;
			}
			nums = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn,ps,null);
		}
		return nums;
	}
	
	
	/**
	 * 执行存储过程
	 */
	public static void executeProcedure() {
		try {
			conn = getConnection();
			CallableStatement cs = conn.prepareCall("{call astro(?,?)}");
			cs.setObject(1, "101");
			cs.registerOutParameter(2, Types.VARCHAR);
			cs.execute();
			System.out.println(cs.getObject(2));//获取出参的数据
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 用普通的Statement执行增删改
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int executeUpdate(String sql) {
		int nums = 0;
		
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			nums = stmt.executeUpdate(sql);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return nums;
	}
	
	/**
	 * 执行SQL查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public static ResultSet executeQuery(String sql, Object... params) {
		try {
			conn = JDBCUtils.getConnection();
			ps = conn.prepareStatement(sql);
			int i=0;
			for(Object p : params) {
				ps.setObject(i+1, p);//给?占位符绑定参数值,从1开始
				i++;
			}
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 释放连接
	 * @param conn
	 */
	public static void close(Connection conn) {
		try {
			if(conn!=null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	/**
	 * 释放资源
	 * @param conn
	 */
	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		try {
			if(conn!=null) {
				conn.close();
				conn = null;
			}
			if(stmt!=null) {
				stmt.close();
				stmt = null;
			}
			if(rs!=null) {
				rs.close();
				rs = null;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	
	public static void main(String[] args) {
		System.out.println(getConnection());
	}

}